/** 
 * 公用方法类
 * @ClassName   类名：MethodUtil 
 * @Description 功能说明：公用方法类
 * TODO
 ************************************************************************
 * @date        创建日期：2012-4-9
 * @author      创建人：陈金滨
 * @version     版本号：V1.0
 ***************************修订记录*************************************
 * 
 *   2012-4-9  陈金滨  创建类
 *   2012-8-10 陈金滨  增加基础资料导入导出方法，自动获取id函数
 *   
 ***********************************************************************
 */
package com.bs.areport.service.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import com.bs.restframework.context.SystemContext;
import com.bs.restframework.context.util.SystemContextUtil;
import com.bs.restframework.db.Database;
import com.bs.restframework.util.GUID;

import net.sourceforge.pinyin4j.PinyinHelper; 
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType; 
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat; 
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType; 
import net.sourceforge.pinyin4j.format.HanyuPinyinVCharType; 

/**
 * 
 * <p>函数名称：通用方法服务类</p>
 * <p>创建时间：2012-4-9</p>
 * <p>作者：陈金滨</p>
 */
@SuppressWarnings("unused")
public class MethodUtil {
	
	private static String chrgJC ;
	private static String departJC ;
	
	public static String CreateGuid()
	{
		UUID uuid = UUID.randomUUID(); 
		String vGuid = uuid.toString(); 
		return vGuid;
	}
	
	/**
	 * 
	 * <p>函数名称：getInfoByID</p>
	 * <p>功能说明：通过ID查找信息</p>
	 * <p>参数说明：id:ID</p>
	 * <p>创建时间：2012-03-26</p>
	 * <p>作者：陈金滨</p>
	 */
	@SuppressWarnings("unchecked")
	public static Map<String, Object>  getDeptInfoByID(String id,Database db) throws Exception
	{	
		String sql="SELECT * FROM "+EntityType.ARP_BSC_DEPT_TABLE+" WHERE F_ID='"+id+"'";
        Map<String, Object> vMap=db.queryObject(Map.class, sql, null);
		return vMap;
	}		
	/**
	 * 
	 * <p>函数名称：isUpCodeExist</p>
	 * <p>功能说明：判断上级编码是否存在</p>
	 * <p>参数说明：String code,String tableName,String vJC,int vLevel,Database db</p>
	 * <p>创建时间：2012-03-26</p>
	 * <p>作者：陈金滨</p>
	 */
	@SuppressWarnings("unchecked")
	public static boolean isUpCodeExist(String code,String tableName,String vJC,Database db) throws Exception
	{	
		boolean result = true ;
		int codeLen = 0 ;
		int vLevel = getLevel(vJC,code);
		if(vLevel > 1)
		{
			for(int i=0;i<vLevel-1;i++)
			{
				codeLen += Integer.parseInt(vJC.substring(i, i+1));
			}	
			String upCode = code.substring(0, codeLen); 
			
			String sql="SELECT * FROM "+tableName+" WHERE F_CODE='"+upCode+"'";
	        Map<String, Object> vMap=db.queryObject(Map.class, sql, null);
	        if(vMap==null||"".equals(vMap))
	        	result = false ;
		}
		
		return result;
	}			
	/**
	 * <p>函数名称：getLevel</p>
	 * <p>功能说明：获取级数通用方法</p>
	 * <p>参数：jc:级长  ; code: 编码</p>
	 * <p>创建时间：2012-04-25</p>
	 * <p>作者：陈金滨</p>
	 */	
	public static Integer getLevel(String jc,String code){
		Integer level=0;
		Integer len=0;
		
		 for(int i=0;i<jc.length();i++){
	        	len=len+Integer.parseInt(jc.substring(i, i+1));
	        	if(len==code.length())
	        		level=i+1;
	        }
		
		return level;
	}		
	
	/**
	 * <p>函数名称：getDeptJC</p>
	 * <p>功能说明：获取单位级长</p>
	 * <p>参数：Database db</p>
	 * <p>创建时间：2012-04-25</p>
	 * <p>作者：陈金滨</p>
	 */	
	@SuppressWarnings("unchecked")
	public static String getDeptJC(Database db) {	
		if(departJC==null||"".equals(departJC))
		{
			String sql = " SELECT F_ITEM1 FROM "+EntityType.ARP_SET_TABLE+" WHERE F_OBJ='单位编码级长'";
			Map<String, Object> vMap = db.queryObject(Map.class, sql,null); 
			String DWJC = "";
	        if((vMap==null)||(vMap.isEmpty()))
	        {
	        	DWJC = "333" ;	        	
	        }
	        else
	        {
	        	DWJC = (String) vMap.get("F_ITEM1");
	        }	
	        departJC = DWJC ;
		}	
		return departJC;
	}

	/**	 
	 * <p>函数名称：setDeptJC</p>
	 * <p>功能说明：设置单位级长</p>
	 * <p>参数：Database db,String deptJC</p>
	 * <p>创建时间：2012-04-25</p>
	 * <p>作者：陈金滨</p>
	 */	
	public static void setDeptJC(Database db,String deptJC) {	

		String vsql = "DELETE FROM "+EntityType.ARP_SET_TABLE+" WHERE F_OBJ='单位编码级长'" ;
		db.update(vsql);
		String sql = "INSERT INTO "+EntityType.ARP_SET_TABLE+"(F_ID,F_OBJ,F_ITEM1) VALUES('"+GUID.newGUID()+"','单位编码级长','"+deptJC+"')" ;
		db.update(sql);
		departJC = deptJC;
	}	
	
	/**	 
	 * <p>函数名称：getCharghJC</p>
	 * <p>功能说明：获取项目级长</p>
	 * <p>参数：Database db</p>
	 * <p>创建时间：2012-04-25</p>
	 * <p>作者：陈金滨</p>
	 */		
	@SuppressWarnings("unchecked")
	public static String getCharghJC(Database db) {
		if(chrgJC==null||"".equals(chrgJC))
		{
			String sql = " SELECT F_ITEM1 FROM "+EntityType.ARP_SET_TABLE+" WHERE F_OBJ='收费项目级长'";
			Map<String, Object> vMap = db.queryObject(Map.class, sql,null); 
			String CHRJC = "" ;
	        if((vMap==null)||(vMap.isEmpty()))
	        {
	        	CHRJC = "333" ;	        	
	        }
	        else
	        {
	        	CHRJC = (String) vMap.get("F_ITEM1");
	        }	
	        chrgJC = CHRJC ;
		}	
		return chrgJC;
	}

	/**	 
	 * <p>函数名称：setCharghJC</p>
	 * <p>功能说明：设置项目级长</p>
	 * <p>参数：Database db,String charghJC</p>
	 * <p>创建时间：2012-04-25</p>
	 * <p>作者：陈金滨</p>
	 */		
	public static void setCharghJC(Database db,String charghJC) {
		String dsql = "DELETE FROM "+EntityType.ARP_SET_TABLE+" WHERE F_OBJ='收费项目级长'" ;
		db.update(dsql);
		String sql = "INSERT INTO "+EntityType.ARP_SET_TABLE+"(F_ID,F_OBJ,F_ITEM1) VALUES('"+GUID.newGUID()+"','收费项目级长','"+charghJC+"')" ;
		db.update(sql);
		chrgJC = charghJC;
	}
	
	/**	 
	 * <p>函数名称：getExcel</p>
	 * <p>功能说明：生成Excel表</p>
	 * <p>参数：tableName 表名，db 数据源</p>
	 * <p>创建时间：2012-08-10</p>
	 * <p>作者：陈金滨</p>
	 */			
	public static String getExcel(String tableName,String vCode,Database db)
	{
		//所需要录入的字段
		String fileter = "" ;
		String fileName = "" ;
		String msg = "" ;		
        SystemContextUtil.getSystemContext();
		Object sysBean = SystemContext.getBean("dataSource");	

		try
		{
			//创建Excel工作簿
			HSSFWorkbook myBook = new HSSFWorkbook();			
			HSSFSheet sheet = myBook.createSheet();		
			
			
			HSSFCell cell;
			HSSFRow row = sheet.createRow(0);
			
			//设置字体
			HSSFFont font = myBook.createFont(); 
			font.setColor(HSSFFont.COLOR_RED); 
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   
			
			
			//设置单元格样式
			HSSFCellStyle cellStyle= myBook.createCellStyle(); 
			cellStyle.setFont(font);
			
			
			HSSFCellStyle cellStyleCol= myBook.createCellStyle(); 
            HSSFDataFormat format = myBook.createDataFormat();

            cellStyleCol.setDataFormat(format.getFormat("@"));
            
            
			HSSFCellStyle style1 = myBook.createCellStyle();// cell样式          
			// 设置单元格背景色，设置单元格背景色以下两句必须同时设置         
			style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式SOLID_FOREGROUND          
			style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色      
			
			// 设置单元格上、下、左、右的边框线 
            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);          
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);          
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);          
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);    
            
            
		    //执行数据库连接		
			Connection conn = db.getConnection();
			Statement statement = conn.createStatement();
			String condition = "";
			String SQL = "" ;      
			
		    if("ARP_BSC_DEPT".equals(tableName))	
		    {		   
		    	//文档     1 - 4 行写入文档说明
		    	cell = row.createCell(0);    
		        cell.setCellValue("文档说明：");    
		        cell.setCellStyle(cellStyle); 
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	            
	            row = sheet.createRow(1);
		    	cell = row.createCell(0);    
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("1.单位编码、单位名称为必填项，不能为空.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            
	            row = sheet.createRow(2);
		    	cell = row.createCell(0);    
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("2.导入前请备份原始数据，数据导入后将覆盖相同编码数据.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            
	            row = sheet.createRow(3);
		    	cell = row.createCell(0);   
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("3.说明文字及字段名称不可删除，请从第六行开始填写数据.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  		            
	            
	            //所需查询字段
		    	fileter = "F_CODE,F_NAME,F_LINKMAN,F_PHONE,F_ADDR,F_PURCNO,F_PURCDATE,";
		    	fileter = fileter+"F_UNITYCODE,F_MEMO,F_BANK,F_ACCOUNT,F_NTCODE,F_NTNAME";
			
		    	//字段名称
		    	row = sheet.createRow(4);
		    	row.setHeight((short) 500);
		    	cell = row.createCell(0);    
		        cell.setCellValue("单位编码");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            cell.setCellStyle(style1);	            
		    	cell = row.createCell(1);    
		        cell.setCellValue("单位名称");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);	            
		    	cell = row.createCell(2);    		    	
		        cell.setCellValue("联系人");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(3);    
		        cell.setCellValue("联系电话");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(4);    
		        cell.setCellValue("地址");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(5);    
		        cell.setCellValue("准购证号码");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(6);    
		        cell.setCellValue("办证日期");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(7);    
		        cell.setCellValue("统一编码");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(8);    
		        cell.setCellValue("备注");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(9);    
		        cell.setCellValue("开户银行");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
	            cell.setCellStyle(style1);
		    	cell = row.createCell(10);    
		        cell.setCellValue("银行账号");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(11);    
		        cell.setCellValue("执收单位编码");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);	           
		    	cell = row.createCell(12);    
		        cell.setCellValue("执收单位名称");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
	            
	            if("ALLDEPT".equals(vCode))
	            	condition = "  " ;	
	            else
	              condition = " WHERE F_CODE LIKE '"+vCode+"%' " ;
	            SQL = "SELECT "+fileter+" FROM "+tableName+condition+" ORDER BY F_CODE ";     
	    	
		    }else if("ARP_BSC_CHARGE".equals(tableName))
		    {
		    	//文档     1 - 4 行写入文档说明
		    	cell = row.createCell(0);    
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("文档说明：");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
	            
	            row = sheet.createRow(1);
		    	cell = row.createCell(0);    
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("1.说明文字及字段名称不可删除，请从第六行开始填写数据；录入请按如下规则填写以免无法导入所有数据.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            
	            row = sheet.createRow(2);
		    	cell = row.createCell(0);  
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("2.项目编码、项目名称为必填项，不能为空。收费性质为：预算内，预算外，其他；是否代收，1或者0，其中1表示代收项目，0表示非代收项目.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            
	            row = sheet.createRow(3);
		    	cell = row.createCell(0);  
		    	cell.setCellStyle(cellStyle); 
		        cell.setCellValue("3.分成比例为(中央：省：市：县)分成，用冒号分隔，如：30：30：20：20；上缴比例为0～100，100为全部上缴，空表示不上缴.");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            
	            //需要查询的字段
		    	fileter = "F_CODE,F_NAME,F_CHRGATTR,F_MSRUNIT,F_FEEGIST,F_PAYRATIO,F_DIVIRATIO,F_ISCOLL";
		    	
		    	//字段名称
		    	row = sheet.createRow(4);
		    	row.setHeight((short) 500);
		    	cell = row.createCell(0);    
		        cell.setCellValue("项目编码");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  	
	            cell.setCellStyle(style1);
		    	cell = row.createCell(1);    
		        cell.setCellValue("项目名称");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(2);    
		        cell.setCellValue("收费性质");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(3);    
		        cell.setCellValue("计量单位");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(4);    
		        cell.setCellValue("收费依据");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(5);    
		        cell.setCellValue("上缴比例");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
	            cell.setCellStyle(style1);
		    	cell = row.createCell(6);    
		        cell.setCellValue("分成比例");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
		    	cell = row.createCell(7);    
		        cell.setCellValue("是否代收");    
	            cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	            cell.setCellStyle(style1);
	            
	            SQL = "SELECT "+fileter+" FROM "+tableName+condition+" ORDER BY F_CODE ";
		    
		    }  
			
			//查询数据库
		    ResultSet rs = statement.executeQuery(SQL); 
			ResultSetMetaData md = rs.getMetaData();
			//获取列数
			int nColumn = md.getColumnCount();
			sheet.setDefaultColumnWidth(16);
			// 写入各个字段的名称
			for (int i = 1; i <= nColumn; i++)
			{
//				cell = row.createCell((i - 1));
//				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				sheet.setColumnWidth(i-1, 3000);
//				sheet.autoSizeColumn(i);
				sheet.setDefaultColumnStyle(i-1, cellStyleCol);
				
			}			
			
			int iRow = 5;
			String cellString = "" ;

			// 写入各条记录，每条记录对应Excel中的一行	
			while(rs.next())
			{
				row = sheet.createRow(iRow);				
				for (int j = 1; j <= nColumn; j++)
				{
					cell = row.createCell(j - 1);						
//					if (rs.getObject(j) instanceof String)
//					{						
//						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//						cell.setCellStyle(cellStyle);
						//判断如果字段为NULL,则将字段更改为"",否则插入数据库时会出错
						if(rs.getObject(j)==null)
							cellString="" ;
						else
						    cellString = rs.getObject(j).toString() ;						
						cell.setCellValue(cellString);				
//						
//					}
				}
				iRow++;				
			}
			//关闭数据集
            rs.close();
    		//当前系统时间为文件名
            Date currentTime = new Date();
    	    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmsssss");
    	    String dateString = formatter.format(currentTime);		            
            fileName = dateString+".xls" ;
			FileOutputStream out = new FileOutputStream(ServletActionContext
					.getServletContext().getRealPath("")
					+ "/upload/" + fileName);
			//将excel输出
			myBook.write(out);	
			out.flush();
			out.close();
			//断开数据库连接
			conn.close();
			System.out.println("导出成功！");
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		if("".equals(msg))			
			msg = fileName ;		
		return msg;
	}
	/**	 
	 * <p>函数名称：changeLevel</p>
	 * <p>功能说明：修改是否最细级字段</p>
	 * <p>参数：tableName 表名，db 数据源</p>
	 * <p>创建时间：2012-08-10</p>
	 * <p>作者：陈金滨</p>
	 */		
	public static void changeLevel(String tableName,Database db) throws Exception
	{
		String code = "" ;
		int indexLV1 = 0 ;
		Object vLevel = 1 ;
		String Level = "" ;
		//查询所有记录。
		String sql = "SELECT F_CODE,F_LEVEL FROM "+tableName+" ORDER BY F_CODE ";
		List<Map> LV1 = db.queryList(Map.class, sql, null) ;
		Map<Object, String> map = null ;
		int rowCount = LV1.size() ;
		while(indexLV1<=rowCount-1)
		{
			map = LV1.get(indexLV1);
			if(map!=null&&!"".equals(map))
			{
				code =  map.get("F_CODE") ;
				vLevel = map.get("F_LEVEL") ;
				Level =  vLevel.toString() ;
				//如果是最细级 ，则直接修改最细级字段
				if(!ifDownCode(tableName,db,Level,code))
				{
					String vSql = "UPDATE "+tableName +" SET F_ISLEAF='1' WHERE F_CODE="+code ;
					db.update(vSql) ;
				}	
			}	
			indexLV1++;
		}		
	}
	/**	 
	 * <p>函数名称：ifDownCode</p>
	 * <p>功能说明：判断是否最细级</p>
	 * <p>参数：tableName 表名，db 数据源，Level 级数，code 编码</p>
	 * <p>创建时间：2012-08-10</p>
	 * <p>作者：陈金滨</p>
	 */			
	public static boolean ifDownCode(String tableName,Database db,String Level,String code) throws Exception
	{
		boolean result = true ;
		
		String sql = " SELECT COUNT(*) FROM "+tableName +" WHERE F_CODE LIKE '"+code+"%' AND F_CODE<>'"+code+"' " ;
		Map<Object, String> resultBean = db.queryObject(Map.class, sql, null);
		String _Count = "" ;
		Object vCount = null ;
		if(resultBean!=null&&!"".equals(resultBean))
		{
			vCount = resultBean.get("COUNT(*)") ;
			_Count = vCount.toString() ;
		}
		int rowCount = 0 ;
		if(_Count!=null&&!"".equals(_Count))
		  rowCount = Integer.parseInt(_Count) ;
		//如果没有下级，既是最细级
	    if(rowCount==0)
	    	result = false ;
		
		return result ;
		
	}
	/**	 
	 * <p>函数名称：excelToDB</p>
	 * <p>功能说明：读取Excel表内容，并插入到数据库中</p>
	 * <p>参数：tableName 表名，fileName 上传文件名，db 数据源，vJC 级长</p>
	 * <p>创建时间：2012-08-10</p>
	 * <p>作者：陈金滨</p>
	 */			
	public static String excelToDB(String tableName,String fileName,Database db,String vJC) throws Exception
	{
		String msg = "" ;  //执行结果信息
		String fileter = "" ;  //字段列表
		String typeFileter = "" ; //不同表对应字段列表
		String typeValues = "" ; //不同字段对应值类表	
		int rowID = 5 ;
		int maxJC = vJC.length();
		Connection conn = db.getConnection();
		Statement statement = conn.createStatement();		
		try
		{
		    //数据库连接
//			Class.forName(DBDRIVER);

			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
					ServletActionContext.getServletContext().getRealPath("")
							+ "/upload/" + fileName+".xls"));
			// 在Excel文档中，第一张工作表的缺省索引是0
			HSSFSheet sheet = wookbook.getSheet("Sheet0");
		
			
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			
			//计算当前文档的列数，判断是否导入正确数据
			HSSFRow rowCol = sheet.getRow(4);
			int colNum = rowCol.getPhysicalNumberOfCells();		
			String table =  "" ;
			//表字段长度
			int[] deptByte=new int[]{50,120,30,30,200,30,8,50,1000,80,120,50,120};
			int[] chargeByte=new int[]{50,120,80,30,80,22,50,1};	
			int[] cellsByte = deptByte ;
			//插入字段列表
		    if("ARP_BSC_DEPT".equals(tableName))	
		    {
		    	fileter = "F_CODE,F_NAME,F_LINKMAN,F_PHONE,F_ADDR,F_PURCNO,F_PURCDATE,";
		    	fileter = fileter+"F_UNITYCODE,F_MEMO,F_BANK,F_ACCOUNT,F_NTCODE,F_NTNAME";	
		    	colNum = 13 ;
		    	table = "单位编码" ;
		    		   
		    	
		    }
		    else if("ARP_BSC_CHARGE".equals(tableName))
		    {
		    	fileter = "F_CODE,F_NAME,F_CHRGATTR,F_MSRUNIT,F_FEEGIST,F_PAYRATIO,F_DIVIRATIO,F_ISCOLL";
		    	colNum = 8 ;
		    	table = "项目编码" ;
		    	cellsByte = chargeByte;
		    	 	
		    }		
		    
			HSSFCell cellTitle = rowCol.getCell(0);
		    String  titleVaule = cellTitle.getStringCellValue();   
		    //文件格式列错误，如导入使用单位却选择了收费项目的文件
		    if(table.equals(titleVaule)==false)
		    {
		    	if("单位编码".equals(table))
		    		msg = "该文件不符合【使用单位】文件格式,请重新选择" ;
		    	else
		    		msg = "该文件不符合【收费项目】文件格式,请重新选择" ;		    	
		    }
		    	
			if("".equals(msg))
			{	
				//预先取得数据库中所有数据，保存数据
				Statement stateFID = conn.createStatement();	
				ResultSet rsFID = stateFID.executeQuery("SELECT * FROM "+tableName+" ORDER BY F_CODE ");
				ResultSetMetaData mdFID = rsFID.getMetaData();
				String sql = "SELECT * FROM "+tableName+" ORDER BY F_CODE " ;
				List<Map> list= db.queryList(Map.class,sql , null) ;
				int RowCount = 5 ;
				
				Object[][] codeList=new String[rows][colNum];
				for(int i=5;i<rows;i++)
				{ 
					HSSFRow row = sheet.getRow(i);		
                    if(row==null)
                    	break ;                    
                    //每行单元格数
					int cells = row.getPhysicalNumberOfCells();
					//如果单元格数超出实际有数值的单元格数，则强制赋值
					if("ARP_BSC_DEPT".equals(tableName))
					{
						if(cells>13)
							cells=13;	
					}else
					{
						if(cells>8)
							cells = 8;
					}
					
					
					//判断空行(整行都为空值)
					String cellFlag = "" ;
					for(int j=0;j<cells;j++)
					{ 												
						HSSFCell cell = row.getCell(j);		
						if (cell != null)
						{
							switch (cell.getCellType())
							{
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								cellFlag += cell.getNumericCellValue() ;    									
								break;
							case HSSFCell.CELL_TYPE_STRING:
								cellFlag += cell.getStringCellValue().trim() ;    									
								break;
							default:
								cellFlag += "";    									
								break;
							}
						}
				    }
					//如果是非空行,则将每个单元格值加入到数组中
					if(!"".equals(cellFlag))
					{
						RowCount ++ ;
						System.out.println(RowCount);
						for(int j=0;j<cells;j++)
						{ 												
							HSSFCell cell = row.getCell(j);
							if (cell != null)
							{
								switch (cell.getCellType())
								{
								case HSSFCell.CELL_TYPE_FORMULA:
									break;
								case HSSFCell.CELL_TYPE_NUMERIC:
									double cellval = cell.getNumericCellValue() ;									
									String cellvale = double2String(cellval,1);//HSSFDateUtil.getJavaDate(cellval).toString();
									codeList[i-5][j] = cellvale ;    									
									break;
								case HSSFCell.CELL_TYPE_STRING:
									codeList[i-5][j] = cell.getStringCellValue().trim() ;    									
									break;
								default:
									codeList[i-5][j] = "";    									
									break;
								}
							}				    
					    }
					}
				} 
				
				
				//导入前检测文件内容是否合法
				String reCode = "" ;//重复编码
				String emptyCode = "" ; //编码为空
				String emptyName = "" ; //名称为空
				String wrongLevel = "" ; //错误编码级长
				String lenError = "" ; //长度错误
				String upCodeError = "" ; //不存在上级编码错误
				boolean isEmpty = false ;//是否空数据
				if(RowCount==5)
					isEmpty = true ;
			    for(int i=0;i<RowCount-5;i++)
			    {
			    	String code = (String) codeList[i][0] ;
			    	String name = (String) codeList[i][1] ;
			    	//如果编码不存在，强制为空值
			    	if(code==null)
		    		{
		    			code="";
		    			codeList[i][0] = "" ;
		    		}	
			    	
			    	//循环遍历数组，记录重复数据
			    	for(int j=i+1;j<RowCount-5;j++)
			    	{
			    		String vCode = (String) codeList[j][0] ;
			    		if(vCode==null)
			    			vCode = "" ;
			    		if(code.equals(codeList[j][0])&&!"".equals(code))
			    		{
			    			reCode = reCode + String.valueOf(j+6)+",";
			    			break;
			    		}			    						  
			    	}
			    	//判断编码是否为空
			    	if("".equals(code))
			    		emptyCode = emptyCode + String.valueOf(i+6) + ",";
			    	//判断名称是否为空
			    	if("".equals(name))
			    		emptyName = emptyCode + String.valueOf(i+6) + ",";
			    	//计算编码级数				
			    	System.out.println(String.valueOf(i)+"--"+code);
					int Level = getLevel(vJC,code);									
					if(Level==0) //如果级数为 0 ，则表示级长不符合规范，提示错误
					{
						wrongLevel += String.valueOf(i+6)+",";						
						break ;
					}
					//判断记录字段长度是否超出数据库字段范围
					if("".equals(lenError))
					{
				    	for(int j=0;j<colNum;j++)
				    	{
				    		if(codeList[i][j]!=null&&!"".equals(codeList[i][j]))
				    		{
					    		int valueLen = codeList[i][j].toString().getBytes().length; //判断字节数，中文汉字为两字节
					    		if(valueLen>cellsByte[j])
					    		{
					    			lenError = "第 "+String.valueOf(i+6)+" 行记录值：" + codeList[i][j].toString()+" 超出字段范围，请核对后重新导入!"  ;
					    		}	
				    		}	
				    	}						
					}
			    }
			    //提示信息
			    if(isEmpty)
			    	msg = "导入文件不能为空，请填写数据后再执行导入!" ;
				if(!"".equals(reCode)&&"".equals(msg))
                {
                	reCode = reCode.substring(0, reCode.length()-1);
                	msg = msg + "第  "+ reCode +" 行记录编码重复,请核对后重新导入! \n" ;
                }                	
                if(!"".equals(emptyCode)&&"".equals(msg))
                {
                	emptyCode = emptyCode.substring(0, emptyCode.length()-1) ;
                	msg = msg + "第  "+ emptyCode +" 行记录编码不能为空,请核对后重新导入! \n" ;	
                }                	
                if(!"".equals(emptyName)&&"".equals(msg))
                {
                	emptyName = emptyName.substring(0, emptyName.length()-1) ;
                	msg = msg + "第  "+ emptyName +" 行记录名称不能为空,请核对后重新导入! \n" ;	
                }                	  
                if(!"".equals(wrongLevel)&&"".equals(emptyCode)&&"".equals(msg))
                {
                	wrongLevel = wrongLevel.substring(0, wrongLevel.length()-1);
                	msg = msg + "第  "+ wrongLevel+" 行记录编码与级长【"+vJC+"】不符，请核对信息后重新导入! \n" ;	
                }
                if(!"".equals(lenError)&&"".equals(msg))
                {
                	lenError = lenError.substring(0, lenError.length()-1) ;
                	msg = msg + lenError+" \n" ;
                }                
				//如果提示信息为空，则表示文件检测无误，开始执行导入操作
                if("".equals(msg))
                {
    				// 遍历行
    				for (int i = 5; i < RowCount; i++)
    				{
    					// 读取左上端单元格
    					HSSFRow row = sheet.getRow(i);
    					// 行不为空
    					if (row != null)
    					{
    						// 获取到Excel文件中的所有的单元格
    						int cells = row.getPhysicalNumberOfCells();
    						String value = "";
    						String cellValue = "" ;
    						String valueLen = "" ;
    						// 遍历列
    						for (int j = 0; j < cells; j++)
    						{
    							// 获取到列的值
    							HSSFCell cell = row.getCell(j);
    							if (cell != null)
    							{
    								switch (cell.getCellType())
    								{
    								case HSSFCell.CELL_TYPE_FORMULA:
    									break;
    								case HSSFCell.CELL_TYPE_NUMERIC:    									 
    									double cellval = cell.getNumericCellValue() ;									
    									value = double2String(cellval,1)+ ","; //Double 转换为 String    									 
    									break;
    								case HSSFCell.CELL_TYPE_STRING:
    									value += cell.getStringCellValue() + ",";    									
    									break;
    								default:
    									value += ""+",";    									
    									break;
    								}
    							}

    						}
    						//空行跳过
    						if("".equals(value))
    							continue ;
    						System.out.println(value);
    						//将value 分隔成数组
    						String[] val = value.split(",",cells+1);
    						cells = val.length;
    						if("ARP_BSC_DEPT".equals(tableName))
    						{
    							if(cells>13)
    								cells = 13;
    						}else
    						{
    							if(cells>8)
    								cells=8;
    						}
    							

    						String[] filter = fileter.split(",",colNum);
    						//自动ID
    						String vID = "" ;; 
    						//最细级
    						String isLeaf = "0" ;
    						
							//编码不为空，则匹配数据库中是否已存在此编码的数据，如果存在，则id 为原库中id
							for(int m=0;m<list.size();m++)
							{
								Map<String,Object> vMap = list.get(m); 
								String code = (String) vMap.get("F_CODE");
								if(code.equals(val[0]))
								{
									vID = (String) vMap.get("F_ID");
									break;
								}
								   
							}    						  						
    						
    						//最后修改时间 
    					    Date currentTime = new Date();
    					    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmsssss");
    					    String dateString = formatter.format(currentTime);
    					    typeFileter = "" ;
    					    typeValues = "";
    						if("".equals(vID))
    						{
    							vID = CreateGuid() ;
    						}							
    						else
    						{
    							String vsql = " Delete From "+tableName +" Where F_CODE='"+val[0]+"'";
    							db.update(vsql);
    						}
    						//级数						
    						int Level = getLevel(vJC,val[0]);
    						String vLevel = String.valueOf(Level) ; 
    						String vZJM = getPinYinHeadChar(val[1]);
    					    typeFileter = typeFileter + "F_ID,F_LEVEL,F_ISLEAF,F_LASTTIME,F_MNEM," ;
    					    typeValues = typeValues + "'"+vID+"','"+vLevel+"','"+isLeaf+"','"+dateString+"','"+vZJM+"',"  ;
    					    //如果是导入使用单位，增加默认字段
    					    if("ARP_BSC_DEPT".equals(tableName))	
    					    {
    					    	typeFileter = typeFileter + "F_TYPE,F_STATE," ;
    					    	typeValues = typeValues + "'单位','0'," ;
    					    }
    					    if(!isUpCodeExist(val[0],tableName,vJC,db))
								msg="第  "+ String.valueOf(rowID+1)+" 行记录不存在上级编码，请核对后重新导入!";
    					    if("".equals(msg))
    					    {
    							//从 Excel 第二行开始读取数据拼接成sql语句
    							if (i != 0)		
    							{    
    								
    								String str = "INSERT INTO "+tableName+"("+typeFileter ;
    								String valList = ") VALUES("+typeValues;
    								for (int k = 0; k < cells; k++)
    								{
    									//为字段添加分号
    									if(val[k]==null)
    										val[k]="" ;
    									valList = valList + "'" + val[k] + "'";
    									
    									str = str + filter[k]  ; 
    									if (k != cells-1)
    									{
    										str += ",";
    										valList += "," ;
    									}
    								}		
    								valList += ")";		
    								str = str + valList ;
    								System.out.println(str);
    		                        //执行sql  
    								db.update(str);
    								rowID++ ;
//    								statement.execute(str);
    							}					    	
    					    }
    					}					
    				}	
                }			
			}
			//更改最细级
			changeLevel(tableName,db);
			if(rowID>5)
			    msg = "已成功导入  "+String.valueOf(rowID-5)+"  条记录! " +msg;				
	
 		} catch (Exception e)
		{
 			changeLevel(tableName,db);			
			e.printStackTrace();
			msg="已成功导入   "+String.valueOf(rowID-5)+"  条记录! "+msg;
			System.out.println(msg);
		}

		System.out.println(msg);
		statement.close() ;
		conn.close() ;
		
		return msg;
	}
	
   /** 
　　* 提取每个汉字的首字母 
　　* @param str 
　　* @return String 
　　*/ 
    public static String getPinYinHeadChar(String str) throws Exception
    { 
	    String result = ""; 
	    HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
	    format.setCaseType(HanyuPinyinCaseType.UPPERCASE);

		for (int j = 0; j < str.length(); j++) 
		{ 
			char word = str.charAt(j); 
			//提取汉字的首字母 
			String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word, format); 
			if (pinyinArray != null) 
			{ 
				result += pinyinArray[0].charAt(0); 
			} 
			else 
			{ 
				result += word; 
			} 
		} 
		return result;
    } 
    /**
     * 函   数  名:double2String
     * 功能说明：Double 转  String
     * @author 陈金滨
     * @param double d,int fNumber
     * @2012-08-24
     */
    public static String double2String(double d, int fNumber) 
    {
         if (fNumber < 0)
             fNumber = 0;
   
	       String pattern = null;
	        switch (fNumber) {
	         case 0:
	             pattern = "#0"; //$NON-NLS-1$
	             break;
	        default:
	            pattern = "#0."; //$NON-NLS-1$
	            StringBuffer b = new StringBuffer(pattern);
	            for (int i = 0; i < fNumber; i++) {
	                b.append('#');
	            }
	            pattern = b.toString();
	            break;
	
	        }
	        DecimalFormat formatter = new DecimalFormat();
	        formatter.applyPattern(pattern);
	        String value = formatter.format(d);
	        return value;
	 }

	
}
